/**
 * 项目名：化工企业安全生产信息化管理平台
 * 日期：    2020-03-17 14:00:30
 * Copyright (c) 2015- -版权所有
 */

package com.joysuch.wwyt.edu.repository;

import java.util.List;

import com.joysuch.wwyt.edu.bean.EduCertUserInfoDto;
import com.joysuch.wwyt.edu.entity.EduCertificateUser;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.transaction.annotation.Transactional;

/**
 * EduCertificateUserDao
 */
public interface EduCertificateUserDao
        extends JpaRepository<EduCertificateUser, Long>, QuerydslPredicateExecutor<EduCertificateUser> {
    public Page<EduCertificateUser> findAll(Specification<EduCertificateUser> spec, Pageable pageable);

    public EduCertificateUser save(EduCertificateUser bean);

    public void delete(EduCertificateUser bean);

    @Query("select count(m) from EduCertificateUser m where certId=?1")
    Long queryUserCountOfCertificate(Long certId);

    @Query("select new com.joysuch.wwyt.edu.bean.EduCertUserInfoDto(ec.id as certId, ec.name as certName,ecu.certNo as certNo,"
            + "ecu.takenTime as takenTime,ecu.outOfDateTime as expireTime) "
            + "from EduCertificateUser ecu left join EduCertificate ec on ecu.certId=ec.id "
            + "where ecu.deleteFlag='0' and ec.deleteFlag='0' and ecu.userId=:userId "
            + "and ecu.userContractorFlag=:userContractorFlag")
    Page<EduCertUserInfoDto> userInfoCert(Long userId, Pageable pageable, Integer userContractorFlag);

    @Query(value = "SELECT * FROM wwyt_edu_certificate_user where USER_ID=?1  and CERT_ID in (?2) and DELETE_FLAG = '0' and CERTIFICATE_STATUS='1' and (OUT_OF_DATE_TIME > now() or OUT_OF_DATE_TIME is null)", nativeQuery = true)
    List<EduCertificateUser> getEffectiveCertByUserIdAndCertIds(Long userId, List<Long> certIds);

    EduCertificateUser findByUserId(Long uid);

    List<EduCertificateUser> findByCertId(Long certId);

    EduCertificateUser findFirstByCertNo(String certNo);

    List<EduCertificateUser> findByCertNoIn(List<String> certNos);

    List<EduCertificateUser> findByCertStatusIsNull();

    void deleteByUserIdAndUserContractorFlag(Long userId, Integer userContractorFlag);

    List<EduCertificateUser> findByUserIdAndCertIdAndUserContractorFlag(Long userId, Long certId,
            Integer userContractorFlag);

    @Transactional
    @Modifying
    @Query(value = "UPDATE wwyt_edu_certificate_user SET delete_flag = '1' WHERE user_id IN ?1 AND user_contractor_flag = ?2",nativeQuery = true)
    void deleteByUserIdsAndUserContractorFlag(List<Long> ids, int i);
}
